﻿Imports System.Data.OleDb
Module Auto_Code
    Public Function AutoID4Access(ByVal SQLStr As String, ByVal TblName As String) As Integer
        Try
            '   Dim sqlConn As String
            If acsconn.State = ConnectionState.Closed Then
                acsconn.Open()
            End If
            Dim MaxRow As Integer
            Dim objDataAdapter As New OleDb.OleDbDataAdapter
            Dim objDataset As New DataSet
            objDataAdapter = New OleDb.OleDbDataAdapter(SQLStr, acsconn)
            objDataAdapter.Fill(objDataset, TblName)
            'acsconn.Close()
            Dim NRow As Integer = objDataset.Tables(TblName).Rows.Count
            If NRow = 0 Then
                MaxRow = objDataset.Tables(TblName).Rows(0).Item(0)
            Else
                MaxRow = NRow
            End If
            Return MaxRow
        Catch ex As SystemException
            MsgBox(ex.Message)
        End Try
    End Function
    Public Sub SaveDataToAccess(ByVal SQLStr As String)
        Try
            Dim cn As New OleDb.OleDbConnection
            cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\hrims.accdb"
            cn.Open()
            Dim myCmd As New OleDb.OleDbCommand
            myCmd.Connection = cn

            myCmd.CommandText = SQLStr
            myCmd.ExecuteNonQuery()
            cn.Close()
            MsgBox("Data Save successfully!")
        Catch ex As Exception
            MsgBox(ex, MsgBoxStyle.Information, "Student Management System Error")
        End Try
    End Sub
    Public Sub DeleteDataAccess(ByVal SQLStr As String)
        Try
            Dim cn As New OleDb.OleDbConnection
            cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\hrims.accdb"
            cn.Open()
            Dim myCmd As New OleDb.OleDbCommand
            myCmd.Connection = cn
            myCmd.CommandText = SQLStr
            myCmd.ExecuteNonQuery()
            cn.Close()
            MsgBox("Data Delete successfully!")
        Catch ex As Exception
            MsgBox(ex, MsgBoxStyle.Information, "HRIMS System Error")
        End Try
    End Sub

    Public Sub LoadDataToLVUsers(ByVal SQL As String, ByVal LV As ListView)
        Dim cn As New OleDbConnection
        Dim DA As OleDbDataAdapter
        Dim DS As New DataSet
        ' Dim dt As DataTable
        Dim dbCmd As OleDbCommand
        cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\hrims.accdb"
        cn.Open()
        dbCmd = New OleDbCommand(SQL, cn)
        DA = New OleDbDataAdapter(dbCmd)
        DS = New DataSet
        DA.Fill(DS)
        LV.Clear()
        Dim j As Integer

        For i = 0 To DS.Tables(0).Rows.Count - 1
            With LV
                .Items.Add(DS.Tables(0).Rows(i)("UserID").ToString)
                .Items(j).SubItems.Add(DS.Tables(0).Rows(i)("KFullName").ToString)
                .Items(j).SubItems.Add(DS.Tables(0).Rows(i)("LFullName").ToString)
                .Items(j).SubItems.Add(DS.Tables(0).Rows(i)("UserLogin").ToString)
                .Items(j).SubItems.Add(DS.Tables(0).Rows(i)("Position").ToString)
            End With
            j = j + 1
        Next
        cn.Close()
        With LV
            .View = View.Details
            .GridLines = True
            .FullRowSelect = True
            .Columns.Add("លេខរៀង", 100, HorizontalAlignment.Center)
            .Columns.Add("ឈ្មោះ​ដេប៉ាតឺម៉ង់​​", 150, HorizontalAlignment.Center)
            .Columns.Add("ឈ្មោះ​ឡាតាំង", 150, HorizontalAlignment.Center)
            .Columns.Add("ឈ្មោះ​ឡក់​អ៊ីន", 100, HorizontalAlignment.Center)
            .Columns.Add("តូនាទី​", 200, HorizontalAlignment.Center)

        End With
    End Sub
    Public Sub LoadDataToLVDept(ByVal SQL As String, ByVal LV As ListView)
        Dim cn As New OleDbConnection
        Dim DA As OleDbDataAdapter
        Dim DS As New DataSet
        ' Dim dt As DataTable
        Dim dbCmd As OleDbCommand
        cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\hrims.accdb"
        cn.Open()
        dbCmd = New OleDbCommand(SQL, cn)
        DA = New OleDbDataAdapter(dbCmd)
        DS = New DataSet
        DA.Fill(DS)
        LV.Clear()
        Dim j As Integer

        For i = 0 To DS.Tables(0).Rows.Count - 1
            With LV
                .Items.Add(DS.Tables(0).Rows(i)("DeptID").ToString)
                .Items(j).SubItems.Add(DS.Tables(0).Rows(i)("DepartmentName").ToString)
                '.Items(j).SubItems.Add(DS.Tables(0).Rows(i)("LFullName").ToString)
                '.Items(j).SubItems.Add(DS.Tables(0).Rows(i)("UserLogin").ToString)
                '.Items(j).SubItems.Add(DS.Tables(0).Rows(i)("Position").ToString)
            End With
            j = j + 1
        Next
        cn.Close()
        With LV
            .View = View.Details
            .GridLines = True
            .FullRowSelect = True
            .Columns.Add("លេខរៀង", 100, HorizontalAlignment.Center)
            .Columns.Add("ឈ្មោះ​ដេប៉ាតឺម៉ង់​​", 150, HorizontalAlignment.Center)
            '.Columns.Add("ឈ្មោះ​ឡាតាំង", 150, HorizontalAlignment.Center)
            '.Columns.Add("ឈ្មោះ​ឡក់​អ៊ីន", 100, HorizontalAlignment.Center)
            '.Columns.Add("តូនាទី​", 200, HorizontalAlignment.Center)

        End With
    End Sub
    Public Sub LoadDataToLVLocation(ByVal SQL As String, ByVal LV As ListView)
        Dim cn As New OleDbConnection
        Dim DA As OleDbDataAdapter
        Dim DS As New DataSet
        ' Dim dt As DataTable
        Dim dbCmd As OleDbCommand
        cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\hrims.accdb"
        cn.Open()
        dbCmd = New OleDbCommand(SQL, cn)
        DA = New OleDbDataAdapter(dbCmd)
        DS = New DataSet
        DA.Fill(DS)
        LV.Clear()
        Dim j As Integer

        For i = 0 To DS.Tables(0).Rows.Count - 1
            With LV
                .Items.Add(DS.Tables(0).Rows(i)("LocationID").ToString)
                .Items(j).SubItems.Add(DS.Tables(0).Rows(i)("LocationName").ToString)
                .Items(j).SubItems.Add(DS.Tables(0).Rows(i)("LocationNote").ToString)
                '.Items(j).SubItems.Add(DS.Tables(0).Rows(i)("UserLogin").ToString)
                '.Items(j).SubItems.Add(DS.Tables(0).Rows(i)("Position").ToString)
            End With
            j = j + 1
        Next
        cn.Close()
        With LV
            .View = View.Details
            .GridLines = True
            .FullRowSelect = True
            .Columns.Add("ល.រ", 50, HorizontalAlignment.Center)
            .Columns.Add("ទីតាំង", 150, HorizontalAlignment.Center)
            .Columns.Add("កត់ចំណាំ", 0, HorizontalAlignment.Center)
            '.Columns.Add("ឈ្មោះ​ឡក់​អ៊ីន", 100, HorizontalAlignment.Center)
            '.Columns.Add("តូនាទី​", 200, HorizontalAlignment.Center)

        End With
    End Sub
    Public Sub LoadDataToLVPermanentStatus(ByVal SQL As String, ByVal LV As ListView)
        Dim cn As New OleDbConnection
        Dim DA As OleDbDataAdapter
        Dim DS As New DataSet
        ' Dim dt As DataTable
        Dim dbCmd As OleDbCommand
        cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\hrims.accdb"
        cn.Open()
        dbCmd = New OleDbCommand(SQL, cn)
        DA = New OleDbDataAdapter(dbCmd)
        DS = New DataSet
        DA.Fill(DS)
        LV.Clear()
        Dim j As Integer

        For i = 0 To DS.Tables(0).Rows.Count - 1
            With LV
                .Items.Add(DS.Tables(0).Rows(i)("PermanentstatusID").ToString)
                .Items(j).SubItems.Add(DS.Tables(0).Rows(i)("PermanentStatus").ToString)
                .Items(j).SubItems.Add(DS.Tables(0).Rows(i)("PermanentNote").ToString)
                '.Items(j).SubItems.Add(DS.Tables(0).Rows(i)("UserLogin").ToString)
                '.Items(j).SubItems.Add(DS.Tables(0).Rows(i)("Position").ToString)
            End With
            j = j + 1
        Next
        cn.Close()
        With LV
            .View = View.Details
            .GridLines = True
            .FullRowSelect = True
            .Columns.Add("ល.រ", 50, HorizontalAlignment.Center)
            .Columns.Add("ក្របក័ណ្ឌការងារ​", 150, HorizontalAlignment.Center)
            .Columns.Add("កត់ចំណាំ", 0, HorizontalAlignment.Center)
            '.Columns.Add("ឈ្មោះ​ឡក់​អ៊ីន", 100, HorizontalAlignment.Center)
            '.Columns.Add("តូនាទី​", 200, HorizontalAlignment.Center)

        End With
    End Sub
   
    Public Sub LoadDataToLVPositions(ByVal SQL As String, ByVal LV As ListView)
        Dim cn As New OleDbConnection
        Dim DA As OleDbDataAdapter
        Dim DS As New DataSet
        ' Dim dt As DataTable
        Dim dbCmd As OleDbCommand
        cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\hrims.accdb"
        cn.Open()
        dbCmd = New OleDbCommand(SQL, cn)
        DA = New OleDbDataAdapter(dbCmd)
        DS = New DataSet
        DA.Fill(DS)
        LV.Clear()
        Dim j As Integer

        For i = 0 To DS.Tables(0).Rows.Count - 1
            With LV
                .Items.Add(DS.Tables(0).Rows(i)("PositionID").ToString)
                .Items(j).SubItems.Add(DS.Tables(0).Rows(i)("PositionStatus").ToString)
                .Items(j).SubItems.Add(DS.Tables(0).Rows(i)("PositionNote").ToString)
                '.Items(j).SubItems.Add(DS.Tables(0).Rows(i)("UserLogin").ToString)
                '.Items(j).SubItems.Add(DS.Tables(0).Rows(i)("Position").ToString)
            End With
            j = j + 1
        Next
        cn.Close()
        With LV
            .View = View.Details
            .GridLines = True
            .FullRowSelect = True
            .Columns.Add("ល.រ", 50, HorizontalAlignment.Center)
            .Columns.Add("មុខតំណែង", 150, HorizontalAlignment.Center)
            .Columns.Add("កត់ចំណាំ", 0, HorizontalAlignment.Center)

            '.Columns.Add("ឈ្មោះ​ឡក់​អ៊ីន", 100, HorizontalAlignment.Center)
            '.Columns.Add("តូនាទី​", 200, HorizontalAlignment.Center)

        End With
    End Sub
    Public Sub LoadDataToLVStaffType(ByVal SQL As String, ByVal LV As ListView)
        Dim cn As New OleDbConnection
        Dim DA As OleDbDataAdapter
        Dim DS As New DataSet
        ' Dim dt As DataTable
        Dim dbCmd As OleDbCommand
        cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\hrims.accdb"
        cn.Open()
        dbCmd = New OleDbCommand(SQL, cn)
        DA = New OleDbDataAdapter(dbCmd)
        DS = New DataSet
        DA.Fill(DS)
        LV.Clear()
        Dim j As Integer

        For i = 0 To DS.Tables(0).Rows.Count - 1
            With LV
                .Items.Add(DS.Tables(0).Rows(i)("StaffTypeID").ToString)
                .Items(j).SubItems.Add(DS.Tables(0).Rows(i)("StaffType").ToString)
                .Items(j).SubItems.Add(DS.Tables(0).Rows(i)("StaffNote").ToString)
                '.Items(j).SubItems.Add(DS.Tables(0).Rows(i)("UserLogin").ToString)
                '.Items(j).SubItems.Add(DS.Tables(0).Rows(i)("Position").ToString)
            End With
            j = j + 1
        Next
        cn.Close()
        With LV
            .View = View.Details
            .GridLines = True
            .FullRowSelect = True
            .Columns.Add("ល.រ", 50, HorizontalAlignment.Center)
            .Columns.Add("ប្រភេទ", 150, HorizontalAlignment.Center)
            .Columns.Add("កត់ចំណាំ", 0, HorizontalAlignment.Center)
            '.Columns.Add("ឈ្មោះ​ឡក់​អ៊ីន", 100, HorizontalAlignment.Center)
            '.Columns.Add("តូនាទី​", 200, HorizontalAlignment.Center)

        End With
    End Sub
    Public Sub LoadDataToLVSalaryCode(ByVal SQL As String, ByVal LV As ListView)
        Dim cn As New OleDbConnection
        Dim DA As OleDbDataAdapter
        Dim DS As New DataSet
        ' Dim dt As DataTable
        Dim dbCmd As OleDbCommand
        cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\hrims.accdb"
        cn.Open()
        dbCmd = New OleDbCommand(SQL, cn)
        DA = New OleDbDataAdapter(dbCmd)
        DS = New DataSet
        DA.Fill(DS)
        LV.Clear()
        Dim j As Integer

        For i = 0 To DS.Tables(0).Rows.Count - 1
            With LV
                .Items.Add(DS.Tables(0).Rows(i)("SalaryCodeID").ToString)
                .Items(j).SubItems.Add(DS.Tables(0).Rows(i)("SalaryCode").ToString)
                .Items(j).SubItems.Add(DS.Tables(0).Rows(i)("Description").ToString)
                '.Items(j).SubItems.Add(DS.Tables(0).Rows(i)("UserLogin").ToString)
                '.Items(j).SubItems.Add(DS.Tables(0).Rows(i)("Position").ToString)
            End With
            j = j + 1
        Next
        cn.Close()
        With LV
            .View = View.Details
            .GridLines = True
            .FullRowSelect = True
            .Columns.Add("ល.រ", 50, HorizontalAlignment.Center)
            .Columns.Add("លេខកូដបៀវត្ស", 150, HorizontalAlignment.Center)
            .Columns.Add("អធិប្បាយ", 150, HorizontalAlignment.Center)

            '.Columns.Add("ឈ្មោះ​ឡក់​អ៊ីន", 100, HorizontalAlignment.Center)
            '.Columns.Add("តូនាទី​", 200, HorizontalAlignment.Center)

        End With
    End Sub

    Public Sub LoadData2AddUser(ByVal SQL As String, ByVal FRM As frmNewUser)
        Dim cn As New OleDbConnection
        Dim DA As OleDbDataAdapter
        Dim DS As New DataSet
        ' Dim dt As DataTable
        Dim dbCmd As OleDbCommand
        cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\hrims.accdb"
        cn.Open()
        dbCmd = New OleDbCommand(SQL, cn)
        DA = New OleDbDataAdapter(dbCmd)
        DS = New DataSet
        DA.Fill(DS)
        'Dim j As Integer
        For i = 0 To DS.Tables(0).Rows.Count - 1
            With FRM
                .txtUserID.Text = (DS.Tables(0).Rows(i)("UserID").ToString)
                .txtKFullName.Text = (DS.Tables(0).Rows(i)("KFullName").ToString)
                .txtEFullName.Text = (DS.Tables(0).Rows(i)("LFullName").ToString)
                .txtUserLogin.Text = (DS.Tables(0).Rows(i)("UserLogin").ToString)
                .txtPosition.Text = (DS.Tables(0).Rows(i)("Position").ToString)
            End With
        Next
        
        cn.Close()
    End Sub

    Public Sub LoadData2DeptText(ByVal SQL As String, ByVal FRMDept As frmDepartement)
        Dim cn As New OleDbConnection
        Dim DA As OleDbDataAdapter
        Dim DS As New DataSet
        ' Dim dt As DataTable
        Dim dbCmd As OleDbCommand
        cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\hrims.accdb"
        cn.Open()
        dbCmd = New OleDbCommand(SQL, cn)
        DA = New OleDbDataAdapter(dbCmd)
        DS = New DataSet
        DA.Fill(DS)
        ' Dim j As Integer
        For i = 0 To DS.Tables(0).Rows.Count - 1
            With FRMDept
                .txtDepID.Text = (DS.Tables(0).Rows(i)("DeptID").ToString)
                .txtDeptDescription.Text = (DS.Tables(0).Rows(i)("DepartmentName").ToString)
                .txtNote.Text = (DS.Tables(0).Rows(i)("Note").ToString)
            End With
        Next

        cn.Close()
    End Sub

    Public Sub LoadDataToCombo(ByVal SQL As String, ByVal MyCombo As ComboBox)
        Dim cn As New OleDbConnection
        Dim DA As OleDbDataAdapter
        Dim DS As New DataSet
        ' Dim dt As DataTable
        Dim dbCmd As OleDbCommand
        cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\hrims.accdb"
        cn.Open()
        dbCmd = New OleDbCommand(SQL, cn)
        DA = New OleDbDataAdapter(dbCmd)
        DS = New DataSet
        DA.Fill(DS)
        MyCombo.Items.Clear()
        Dim myVal As String
        Dim j As Integer
        Dim ID As String


        For i = 0 To DS.Tables(0).Rows.Count - 1
            With MyCombo
                .Items.IndexOf(DS.Tables(0).Rows(i)(0).ToString)
                ID = DS.Tables(0).Rows(i)(0).ToString
                myVal = DS.Tables(0).Rows(i)(1).ToString
                .Items.Add(ID & "-" & myVal)
                '.Items(j).SubItems.Add(DS.Tables(0).Rows(i)(1).ToString)
                '.Items.Add(DS.Tables(0).Rows(i)(1).ToString)
            End With
            j = j + 1
        Next
        cn.Close()

    End Sub

    Public Function GetID(ByVal myText As String) As Integer

        Try
            Return (myText.Substring(0, myText.IndexOf("-")))
        Catch ex As SystemException
            MsgBox(ex.Message)
        End Try
    End Function
    Public Sub LoadDataToLV(ByVal SQLStr As String, ByVal LV As ListView)
        ''   MyConnection()
        Dim F As String
        Dim T As String
        'Dim sqlConn As String
        Dim objDataAdapter As New OleDbDataAdapter
        Dim objDataset As New DataSet
        'Dim FD As String
        '  FD = Format(SRDate, "yyyy-MM-dd")
        '  sqlConn = "SELECT s.sd_id, s.sd_name, s.sd_tel,r.rv_id, r.rv_name, r.rv_tel,t.brand_id,t.send_amount,t.send_service,t.send_date,cr.cy_symbol FROM tbl_sender as s INNER JOIN tbl_transfer as t ON s.sd_id = t.sd_id INNER JOIN tbl_receivers as r ON r.rv_id=t.rv_id INNER JOIN tbl_currency as cr  ON cr.cy_id=t.cy_id WHERE  t.tran_status='" & Tran_Status & "' AND t.brand_id='" & U_brand_id & "' AND t.send_date='" & FD & "'"
        ' sqlConn = "SELECT rv_id,rv_name,rv_sex,rv_tel,rv_address,rv_date FROM tbl_receivers"
        objDataAdapter = New OleDbDataAdapter(SQLStr, acsconn)
        objDataAdapter.Fill(objDataset, SQLStr)
        acsconn.Close()
        Dim NRow As Integer = objDataset.Tables(SQLStr).Rows.Count
        Dim NCol As Integer = objDataset.Tables(SQLStr).Columns.Count
        Dim i As Integer = 0
        Dim j As Integer = 0
        'Dim k As Integer
        Try
            LV.Items.Clear()
            For i = 0 To NRow - 1
                F = objDataset.Tables(SQLStr).Rows(i).Item(0)                 '  
                LV.Items.Add(F)
                For j = 1 To NCol - 1
                    T = objDataset.Tables(SQLStr).Rows(i).Item(j)
                    LV.Items(i).SubItems.Add(T)
                Next j
                '    j = j + 1
            Next i
        Catch ex As SystemException
            MsgBox(ex.Message)
        End Try
    End Sub

    'Public Sub LoadDataToCombo(ByVal SQLStr As String, ByVal MyCombo As ComboBox)
    '    Try
    '        '   Dim sqlConn As String
    '        Dim objDataAdapter As New OleDbDataAdapter
    '        Dim objDataset As New DataSet
    '        objDataAdapter = New OleDbDataAdapter(SQLStr, acsconn)
    '        objDataAdapter.Fill(objDataset, SQLStr)
    '        acsconn.Close()
    '        Dim NRow As Integer = objDataset.Tables(SQLStr).Rows.Count
    '        If NRow = 0 Then
    '            Exit Sub
    '        End If
    '        Dim i As Integer
    '        MyCombo.Items.Clear()
    '        Dim BN As String
    '        Dim BID As Integer
    '        For i = 0 To NRow - 1
    '            BN = objDataset.Tables(SQLStr).Rows(i).Item(1)
    '            BID = objDataset.Tables(SQLStr).Rows(i).Item(0) - 1
    '            'cboPlaces.Items.Insert(BID, BN)
    '            MyCombo.Items.Add(BID + 1 & "-" & BN)
    '            'Dim myString As String
    '            '' myString = (BN & "-" & BID).ToString
    '            'myString = (BID + 1 & "" & BN).ToString
    '            'Dim MyString1 As String = myString.Substring(0, 1)
    '            'Dim myString2 As String
    '            'myString2 = MyString1
    '        Next
    '    Catch ex As SystemException
    '        MsgBox(ex.Message)
    '    End Try
    'End Sub
    Public Sub Load1FieldToCombo(ByVal SQLStr As String, ByVal MyCombo As ComboBox)
        Try
            '   Dim sqlConn As String
            Dim objDataAdapter As New OleDbDataAdapter
            Dim objDataset As New DataSet
            objDataAdapter = New OleDbDataAdapter(SQLStr, acsconn)
            objDataAdapter.Fill(objDataset, SQLStr)
            acsconn.Close()
            Dim NRow As Integer = objDataset.Tables(SQLStr).Rows.Count
            If NRow = 0 Then
                Exit Sub
            End If
            Dim i As Integer
            MyCombo.Items.Clear()
            Dim BN As String
            'Dim BID As Integer
            For i = 0 To NRow - 1
                BN = objDataset.Tables(SQLStr).Rows(i).Item(0)
                'BID = objDataset.Tables(SQLStr).Rows(i).Item(0) - 1
                'cboPlaces.Items.Insert(BID, BN)
                MyCombo.Items.Add(BN.ToString)


            Next
        Catch ex As SystemException
            MsgBox(ex.Message)
        End Try
    End Sub
End Module